package com.github.carter659.spring04;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;
import org.springframework.web.client.RestTemplate;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author  yangyangh
 */
@Repository
public class NewsRespository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private RestTemplate restTemplate;

    public List<Map<String, Object>> findAll(int size, int pageNum,String topic) {

        StringBuilder builder = new StringBuilder();
        builder.append( "SELECT count(nlp_article.ID) as num "+
                "FROM nlp_article  " +
                "WHERE TOPICS like \""+topic+"%\" " );

        SqlRowSet countResult = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
        int count = 0;
        while (countResult.next()) {
            count = countResult.getInt( "num" );
        }
        builder = new StringBuilder();
        builder.append( "SELECT nlp_article.ID, TIT, SUBSTRING_INDEX( SUBSTRING_INDEX( TOPICS, ' ', 1 ), '_', 1 )TOPIC, picture,source, url, PUB_DT, labels,\n" +
                "SUBSTRING_INDEX(SUBSTRING_INDEX(TOPICS, ' ', 1),'_',-1) SCORE ,content,nlp_article.orig_id  " +
                        "FROM nlp_article  " +
                        "WHERE TOPICS like \""+topic+"%\" order by score desc limit " );

        builder.append( size * pageNum + "," + size );
        SqlRowSet rows = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
        Map<String, Object> map;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        while (rows.next()) {
            map = new HashMap<String, Object>();
            builder = new StringBuilder();
            builder.append( "SELECT GROUP_CONCAT(SENTENCE ORDER BY `ORDER` ASC SEPARATOR '。') AS \"SUMMARY\" FROM nlp_summary \n" +
                    "WHERE IMPORTANCE < 99  " +
                    "AND ORIG_ID =" +rows.getLong( "ORIG_ID" )+
                    " group by ORIG_ID" );

            SqlRowSet summeryRow = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
            while (summeryRow.next()) {
                map.put( "summary", summeryRow.getString( "SUMMARY" ) );
            }
            map.put( "id", rows.getLong( "ID" ) );
            map.put( "orig_id", rows.getLong( "ORIG_ID" ) );
            map.put( "url", rows.getString( "URL" ) );
            map.put( "tit", rows.getString( "TIT" ) );
            map.put( "source", rows.getString( "SOURCE" ) );
            map.put( "pubDt", rows.getTimestamp( "PUB_DT" ) );
            map.put( "score",rows.getDouble( "SCORE" ) );
            map.put( "topic", rows.getString( "TOPIC" ) );
            map.put( "content", rows.getString( "CONTENT" ).replace( "||", " " ) );
            map.put( "num", count );
            map.put( "picture", rows.getString( "PICTURE" )  );
            map.put( "topicName",topic );
            String labels =  rows.getString( "LABELS" );
            Map<String,Object> labelMap =null;
            String[] strs = labels.split( " " );
            List<Map<String,Object>> companyLabelsList = new ArrayList<Map<String,Object>>(  );
            List<String> industryLabelsList = new ArrayList<String>(  );
            for(String str :strs){

                if(str !=null && str.length()>1){
                    String companyName =  str.substring( str.indexOf( "|" )+1,str.indexOf( "_" ) );
                    if(str.startsWith( "2" )){

                        builder = new StringBuilder();
                        builder.append("select value from company where name = \'"+companyName+"\'");

                        SqlRowSet companySet = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
                        String comnyCode =null;
                        while (companySet.next()) {
                            comnyCode = companySet.getString( "value" );
                            if(comnyCode !=null &&comnyCode != ""){
                                if(comnyCode.startsWith( "6" )){
                                    comnyCode="sh"+comnyCode;
                                }else if(comnyCode.startsWith( "4")|| comnyCode.startsWith( "8")){
                                    comnyCode="sb"+comnyCode;
                                }else{
                                    comnyCode="sz"+comnyCode;
                                }
                            }
                        }

                        labelMap  = new HashMap<String,Object>();
                        if(comnyCode != null){

                            String result=    this.restTemplate.getForEntity( "http://hq.sinajs.cn/list="+comnyCode,String.class ).getBody();

                            if(result.split( "," ).length>2){
                                Double start_value =Double.valueOf(  result.split( "," )[1]) ;
                                Double now_value =Double.valueOf(  result.split( "," )[3]) ;
                                if(start_value==0){
                                    labelMap.put( "value",0 );
                                    labelMap.put( "status","not_open" );
                                }else{
                                    labelMap.put( "value",now_value/ start_value*100-100);
                                    labelMap.put( "status","open" );
                                }
                            }
                        }
                        //result="var hq_str_sz300519=\"新光药业,35.121,35.430,35.641,0.000,0.000,0.000,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,2018-05-11,15:20:03,03\"";


                        labelMap.put( "name",companyName);

                        companyLabelsList.add( labelMap );
                    }else{

                        industryLabelsList.add( companyName);
                    }

                }
                }


            map.put("labels",companyLabelsList);
            if(industryLabelsList!=null && industryLabelsList.size()>0){
                map.put("industry",industryLabelsList);
            }

            //map.put( "hotScore",rows.getDouble(   "HOT_SCORE" ) );
            list.add( map );
        }
        return list;
    }

    public Map<String, Object> findOne(int id) {
        Map<String, Object> map = new HashMap<String,Object>(  );
        StringBuilder builder = new StringBuilder();
        builder.append( "select SOURCE,PUB_DT,url,tit,LABELS from nlp_article left" +
                " JOIN nlp_summary on nlp_article.ORIG_ID = nlp_summary.ORIG_ID where nlp_article.id = "+id );

        SqlRowSet rowResult = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
        while (rowResult.next()) {
            map.put( "url", rowResult.getString( "URL" ) );
            map.put( "tit", rowResult.getString( "TIT" ) );
           // map.put( "summary", rowResult.getString( "SUMMARY" ) );
            map.put( "source", rowResult.getString( "SOURCE" ) );
            map.put( "pubDt", rowResult.getTimestamp( "PUB_DT" ) );

            String labels =  rowResult.getString( "LABELS" );
            Map<String,Object> labelMap =null;
            String[] strs = labels.split( " " );
            List<Map<String,Object>> companyLabelsList = new ArrayList<Map<String,Object>>(  );
            List<String> industryLabelsList = new ArrayList<String>(  );
            for(String str :strs){
                if(str!=null&&str.length()>1){
                    String companyName =  str.substring( str.indexOf( "|" )+1,str.indexOf( "_" ) );
                    if(str.startsWith( "2" )){

                        builder = new StringBuilder();
                        builder.append("select value from company where name = \'"+companyName+"\'");

                        SqlRowSet companySet = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
                        String comnyCode =null;
                        while (companySet.next()) {
                            comnyCode = companySet.getString( "value" );
                            if(comnyCode !=null &&comnyCode != ""){
                                if(comnyCode.startsWith( "6" )){
                                    comnyCode="sh"+comnyCode;
                                }else if(comnyCode.startsWith( "4")|| comnyCode.startsWith( "8")){
                                    comnyCode="sb"+comnyCode;
                                }else{
                                    comnyCode="sz"+comnyCode;
                                }
                            }
                        }

                        labelMap  = new HashMap<String,Object>();
                        if(comnyCode != null){

                            String result=    this.restTemplate.getForEntity( "http://hq.sinajs.cn/list="+comnyCode,String.class ).getBody();

                            if(result.split( "," ).length>2){
                                Double start_value =Double.valueOf(  result.split( "," )[1]) ;
                                Double now_value =Double.valueOf(  result.split( "," )[3]) ;
                                if(start_value==0){
                                    labelMap.put( "value",0 );
                                    labelMap.put( "status","not_open" );
                                }else{
                                    labelMap.put( "value",now_value/ start_value*100-100);
                                    labelMap.put( "status","open" );
                                }
                            }
                        }
                        //result="var hq_str_sz300519=\"新光药业,35.121,35.430,35.641,0.000,0.000,0.000,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,0,0.000,2018-05-11,15:20:03,03\"";


                        labelMap.put( "name",companyName);

                        companyLabelsList.add( labelMap );
                    }else{

                        industryLabelsList.add( companyName);
                    }

                }
                }

            map.put("labels",companyLabelsList);
            if(industryLabelsList!=null && industryLabelsList.size()>0){
                map.put("industry",industryLabelsList);
            }
           // map.put( "content", rowResult.getString( "CONTENT" ).replace( "||", " " ) );

        }
        String summary  = "";
        builder = new StringBuilder();
        builder.append( "select * from nlp_summary where ORIG_ID =   "+id +" order by `order`" );
        SqlRowSet summaryListResult = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
        StringBuilder summaryIds=new StringBuilder( "" );
        while(summaryListResult.next()){
            summaryIds.append( summaryListResult.getString( "order" )+"," );
            summary +=summaryListResult.getString( "SENTENCE" );
        }
        map.put( "summary",summary );
        builder = new StringBuilder();
        builder.append( "select sid , `order`, text  from nlp_article_textsorted where ORIG_ID =  "+id+" order by `order`" );
        SqlRowSet contentResult = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
        List<Map<String,Object>> contents = new ArrayList<Map<String,Object>>(  );
        Map<String,Object> content = null;
        while(contentResult.next()){
            content = new HashMap<String,Object>(  );
            content.put( "id",contentResult.getString( "sid" ) );
            content.put( "text",contentResult.getString( "text" ) );
            content.put( "order",contentResult.getInt( "order" ) );

            if(summaryIds.indexOf( contentResult.getString( "sid" ) )>=0){
                //是否高亮，true 为是
                content.put( "flag",true );
            }else{
                //fou
                content.put( "flag",false );
            }
            contents.add( content );
        }
        map.put( "contents",contents );
        builder = new StringBuilder();
        builder.append( "select  nlp_topic.TOPIC,nlp_topic.id from nlp_article_topics \n" +
                "left JOIN nlp_topic on nlp_article_topics.TOPIC = nlp_topic.TOPIC where nlp_article_topics.ORIG_ID =   "+id );
        SqlRowSet topicSet = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
        List<Map<String,Object>> topics = new ArrayList<Map<String,Object>>();
        while (topicSet.next()){
            Map<String,Object> topic = new HashMap<String,Object>(  );
            topic.put("name",topicSet.getString( "topic" ));
            topic.put("id",topicSet.getLong( "id" ));
            topics.add( topic );
        }
        map.put( "topics",topics );
        return map;
    }
    public List<Map<String, Object>> findArticleBySentenceId(String sid) {

        List<Map<String,Object>> lists = new ArrayList<Map<String,Object>>(  );

        String result=    this.restTemplate.getForEntity( "http://139.129.246.236:8899/ssing?id="+sid,String.class ).getBody();
        if(result!=null){
            StringBuilder builder = new StringBuilder();
            builder.append( "select sid , `order`, text , nlp_article.id aid, nlp_article.tit from nlp_article_textsorted \n" +
                    "left JOIN nlp_article on nlp_article.ORIG_ID = nlp_article_textsorted.ORIG_ID where sid in( '"+result.replace( ",","','" )+" ')" );

            SqlRowSet rowResult = jdbcTemplate.queryForRowSet( builder.toString().toLowerCase(), new Object[]{} );
            Map<String,Object> map = null;
            while(rowResult.next()){
                map = new HashMap<String,Object>();
                map.put( "sid",rowResult.getString( "sid" ) );
                map.put( "text",rowResult.getString( "text" ) );
                map.put( "tit",rowResult.getString( "tit" ) );
                map.put("aid",rowResult.getInt( "aid" ));
                lists.add( map );
            }
        }
        return lists;

    }

}